package com.fox.hiring.common.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;
import java.util.ResourceBundle;

import org.apache.commons.lang3.Validate;

import com.fox.hiring.common.DBObject;

/**
 * 数据的相关操作工具类
 * 
 * @author Administrator
 *
 */
public class DBUtil {

	private static final String DEFAULT_PROPERITES = "jdbc";
	public static final String DRIVER = getString("driver");
	public static final String URL = getString("url");
	public static final String USERNAME = getString("username");
	public static final String PASSWORD = getString("password");

	private DBUtil() {

	}

	private static String getString(String key) {
		return ResourceBundle.getBundle(DEFAULT_PROPERITES).getString(key);
	}

	/**
	 * 获取数据库连接
	 * 
	 * @return
	 * @throws RuntimeException
	 */
	public static Connection getConnection() {
		Connection connection;
		try {
			// 1）加载驱动。
			Class.forName(DRIVER);
			// 2）连接数据库。
			connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			return connection;
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("获取数据库连接失败！");
		}
	}

	/**
	 * 关闭数据库
	 * 
	 * @param connection
	 * @throws RuntimeException
	 */
	public static void close(Connection connection) {
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("关闭数据库连接失败！");
		}
	}

	/**
	 * 关闭PreparedStatement
	 * 
	 * @param connection
	 * @throws RuntimeException
	 */
	public static void close(PreparedStatement preparedStatement) {
		try {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("关闭PreparedStatement失败！");
		}
	}

	/**
	 * 关闭Statement
	 * 
	 * @param connection
	 * @throws RuntimeException
	 */
	public static void close(Statement statement) {
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("关闭PreparedStatement失败！");
		}
	}

	/**
	 * 关闭ResultSet
	 * 
	 * @param connection
	 * @throws RuntimeException
	 */
	public static void close(ResultSet resultSet) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("关闭ResultSet失败！");
		}
	}

	/**
	 * 关闭操作的资源（增、删、改）
	 * 
	 * @param connection
	 * @throws RuntimeException
	 */
	public static void close(PreparedStatement preparedStatement,
			Connection connection) {
		close(preparedStatement);
		close(connection);
	}

	/**
	 * 关闭操作的资源（增、删、改）
	 * 
	 * @param connection
	 * @throws RuntimeException
	 */
	public static void close(Statement statement, Connection connection) {
		close(statement);
		close(connection);
	}

	/**
	 * 关闭操作的资源（查）
	 * 
	 * @param connection
	 * @throws RuntimeException
	 */
	public static void close(ResultSet resultSet,
			PreparedStatement preparedStatement, Connection connection) {
		close(resultSet);
		close(preparedStatement);
		close(connection);
	}

	/**
	 * 查询操作的资源释放
	 * 
	 * @param connection
	 * @throws RuntimeException
	 */
	public static void close(ResultSet resultSet, Statement statement,
			Connection connection) {
		close(resultSet);
		close(statement);
		close(connection);
	}

	/**
	 * 查询操作的资源释放
	 * 
	 * @param connection
	 * @throws RuntimeException
	 */
	public static void close(DBObject dbObject) {
		if (dbObject != null) {
			close(dbObject.getResultSet());
			close(dbObject.getPreparedStatement());
			close(dbObject.getConnection());
		}
	}

	/**
	 * 数据修改操作
	 * 
	 * @param sql
	 *            DELETE FROM resume WHERE id=?
	 * @param paramList
	 *            1
	 * @return
	 * @throws SQLException
	 */
	public static int executeUpdate(String sql, List<Object> paramList)
			throws SQLException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = getConnection();
			// 3、操作数据库
			preparedStatement = connection.prepareStatement(sql);
			setParameter(paramList, preparedStatement);
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			// 4、关闭资源（先打开的后关闭）
			close(preparedStatement, connection);
		}
	}

	/**
	 * 数据修改操作
	 * 
	 * @param sql
	 * @param paramList
	 * @return
	 * @throws SQLException
	 */
	public static int executeUpdate(String sql, Object... params)
			throws SQLException {
		Validate.notNull(params, "参数不能为null!");
		return executeUpdate(sql, Arrays.asList(params));
	}

	/**
	 * 数据查询操作
	 * 
	 * @param sql
	 * @param paramList
	 * @return
	 * @throws SQLException
	 */
	public static DBObject executeQuery(String sql, List<Object> paramList)
			throws SQLException {
		Connection connection = getConnection();
		// 3、操作数据库
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		setParameter(paramList, preparedStatement);
		ResultSet resultSet = preparedStatement.executeQuery();
		return new DBObject(resultSet, preparedStatement, connection);
	}

	/**
	 * 数据查询操作
	 * 
	 * @param sql
	 * @param paramList
	 * @return
	 * @throws SQLException
	 */
	public static DBObject executeQuery(String sql, Object... params)
			throws SQLException {
		Validate.notNull(params, "参数不能为null!");
		return executeQuery(sql, Arrays.asList(params));
	}

	/**
	 * 参数设置
	 * 
	 * @param paramList
	 * @param preparedStatement
	 * @throws SQLException
	 */
	private static void setParameter(List<Object> paramList,
			PreparedStatement preparedStatement) throws SQLException {
		if (paramList != null) {
			for (int i = 0; i < paramList.size(); i++) {
				preparedStatement.setObject(i + 1, paramList.get(i));
			}
		}
	}
}
